package cn.diaomin.dao.impl;

import cn.diaomin.dao.BaseDao;
import cn.diaomin.dao.WorkingDao;
import cn.diaomin.entity.Working;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.LinkedList;
import java.util.List;

public class WorkingDaoImpl extends BaseDao implements WorkingDao {
    @Override
    public List<Working> getAll() {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Working> workings=new LinkedList<>();
        try {
            con = getConn();
            String sql = "select id,title,content,date_format(createdate,'%Y-%m-%d') as createdate," +
                    "type from working order by createdate;";
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                Working working = new Working(
                        rs.getInt("id"),
                        rs.getString("title"),
                        rs.getString("content"),
                        rs.getDate("createDate"),
                        rs.getInt("type")
                );
                workings.add(working);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll(con, ps, rs);
        }
        return workings;
    }

    @Override
    public int getCount(String title) {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int count=0;
        try {
            con = getConn();
            String sql = "select count(1) from working where title=?";
            ps = con.prepareStatement(sql);
            ps.setString(1,title);
            rs = ps.executeQuery();
            if (rs.next()) {
                count=rs.getInt("count(1)");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll(con, ps, rs);
        }
        return count;
    }

    @Override
    public Working getOne(int id) {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Working working=null;
        try {
            con = getConn();
            String sql = "select id,title,content,date_format(createdate,'%Y-%m-%d') as createdate," +
                    "type from working where id=?";
            ps = con.prepareStatement(sql);
            ps.setInt(1,id);
            rs = ps.executeQuery();
            while (rs.next()) {
                working = new Working(
                        rs.getInt("id"),
                        rs.getString("title"),
                        rs.getString("content"),
                        rs.getDate("createDate"),
                        rs.getInt("type")
                );
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll(con, ps, rs);
        }
        return working;
    }

    @Override
    public int add(Working working) {
        String sql = "insert into working values(default,?,?,default,?)";
        Object[] pm = {working.getTitle(),working.getContent(),working.getType()};
        int rst = executeUpdate(sql, pm);
        return rst;
    }
}
